<?php

namespace App\Console\Commands;

use App\Exports\AccountsCountExport;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;

class ExportAccountsInfo extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'ExportAccountsInfo {date1?} {date2?}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $date1 = $this->argument('date1');
        $date2 = $this->argument('date2');
        if ($date1&&$date2&&$this->checkDateFormat($date1)&&$this->checkDateFormat($date2)){
            $todayDate = $date1;
            $tomorrowDate = $date2;
        }else{
            $todayDate = date('Y-m-d 00:00:00',time());
            $tomorrowDate = date('Y-m-d 00:00:00',strtotime('+1 day'));
        }

        //总计
        $accountsInfoAll = DB::table('meta_accounts')->select(DB::raw('country,count(*) as accounts_count'))
            ->where('create_time','>',$todayDate)
            ->where('create_time','<',$tomorrowDate)
            ->groupBy('country')
            ->orderBy('accounts_count','DESC')
            ->get()
            ->toArray();
        $accountsInfoAll = array_combine(array_column($accountsInfoAll,'country'),array_column($accountsInfoAll,'accounts_count'));

        //账单号 有额度 大额度 有花费 状态是活 关户 欠费 否预付
        $accountsInfoZd = DB::table('meta_accounts')->select(DB::raw('country,count(*) as accounts_count'))
            ->where('create_time','>',$todayDate)
            ->where('create_time','<',$tomorrowDate)
            ->where('adtrust_dsl','!=',0)
            ->where('adtrust_dsl_usd','>=',250)
            ->where('amount_spent','>',0)
            ->whereIn('account_status',[1,3,101])
            ->where('is_prepay_account','=',1)
            ->groupBy('country')
            ->orderBy('accounts_count','DESC')
            ->get()
            ->toArray();
        $accountsInfoZd = array_combine(array_column($accountsInfoZd,'country'),array_column($accountsInfoZd,'accounts_count'));

        //专业号 有page
        $accountsInfoZy = DB::table('meta_accounts')->select(DB::raw('country,count(*) as accounts_count'))
            ->where('create_time','>',$todayDate)
            ->where('create_time','<',$tomorrowDate)
            ->where('page','!=',0)
            ->groupBy('country')
            ->orderBy('accounts_count','DESC')
            ->get()
            ->toArray();
        $accountsInfoZy = array_combine(array_column($accountsInfoZy,'country'),array_column($accountsInfoZy,'accounts_count'));

        //BM号 有bm
        $accountsInfoBm = DB::table('meta_accounts')->select(DB::raw('country,count(*) as accounts_count'))
            ->where('create_time','>',$todayDate)
            ->where('create_time','<',$tomorrowDate)
            ->where('bm','>',0)
            ->groupBy('country')
            ->orderBy('accounts_count','DESC')
            ->get()
            ->toArray();
        $accountsInfoBm = array_combine(array_column($accountsInfoBm,'country'),array_column($accountsInfoBm,'accounts_count'));

        $head = [['国家','总计','账单号','专业号','BM号']];

        $cellData = [];


        foreach ($accountsInfoAll as $country=>$count){
            $newArr = array();
            $newArr[0] = $country;
            $newArr[1] = $count;
            if (in_array($country,array_keys($accountsInfoZd))){
                $newArr[2] = $accountsInfoZd[$country];
            }else{
                $newArr[2]= '';
            }
            if (in_array($country,array_keys($accountsInfoZy))){
                $newArr[3] = $accountsInfoZy[$country];
            }else{
                $newArr[3]= '';
            }
            if (in_array($country,array_keys($accountsInfoBm))){
                $newArr[4] = $accountsInfoBm[$country];
            }else{
                $newArr[4]= '';
            }
            $cellData[] = $newArr;
        }
        $foot = [['总计','=sum(B2:B'.count($cellData).')','=sum(C2:C'.count($cellData).')','=sum(D2:D'.count($cellData).')','=sum(E2:E'.count($cellData).')']];
        $list = array_merge($head,$cellData);
        $list = array_merge($list,$foot);

        $export = new AccountsCountExport([
            $list
        ]);

        $res = Excel::store($export, 'data/'.date('Y-m-d H:i:s',time()).'_accounts.xlsx');
        if ($res){
            echo 'OK';
        }else{
            echo 'FALL';
        }
    }

    private function checkDateFormat($date)
    {
        //匹配日期格式
        if (preg_match ("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date, $parts))
        {
            //检测是否为日期
            if(checkdate($parts[2],$parts[3],$parts[1]))
                return true;
            else
                return false;
        }
        else
            return false;
    }
}
